package it.trekker.db;

import java.io.File;
import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class PhotoDAO {

	private static final String TAG = PhotoDAO.class.getSimpleName();
	
	private static Context context = null;
	private SQLiteDatabase db = null;
	private DatabaseHelper dbHelper = null;
	

	public PhotoDAO(Context context) {
		dbHelper = new DatabaseHelper(context);
	}

	
	public SQLiteDatabase open() {
		if(db == null || !db.isOpen()) {
			db = dbHelper.getWritableDatabase();
			db.execSQL("PRAGMA foreign_keys=ON;");
		}
		
		return db;
	}

	
	public void close() {
		if(db != null && db.isOpen())
			db.close();
	}
	
	
	public boolean isOpen() {
		return db.isOpen();
	}

	
	public int insertPhoto(Photo photo) {

		ContentValues cv = new ContentValues();

		cv.put(PhotoTableMeta.KEY_PATH, photo.getPath());
		cv.put(PhotoTableMeta.KEY_LATITUDE, photo.getLatitude());
		cv.put(PhotoTableMeta.KEY_LONGITUDE, photo.getLongitude());
		cv.put(PhotoTableMeta.KEY_EXCURSIONID, photo.getExcursionId());

		return (int) db.insert(PhotoTableMeta.DATABASE_TABLE, null, cv);
	}

	
	public boolean deletePhoto(long id, boolean deleteFile) {
		Photo photo = getPhoto(id);
		
		boolean fileResult = true;
		boolean dbResult = (db.delete(PhotoTableMeta.DATABASE_TABLE, PhotoTableMeta.KEY_ROWID + "=" + id, null) > 0);
		
		if(deleteFile)
			fileResult = deletePhotoFile(photo.getPath());
		
		return (dbResult && fileResult);
	}
	
	
	public boolean deleteAllPhotosByExcursion(long excursionId, boolean deleteFiles) {
		boolean fileResult = true;
		
		if(deleteFiles)
			fileResult = deleteAllPhotoFilesByExcursion(excursionId);
		
		boolean dbResult = (db.delete(PhotoTableMeta.DATABASE_TABLE, PhotoTableMeta.KEY_EXCURSIONID + "=" + excursionId, null) > 0);
		
		return (fileResult && dbResult);
	}

	
	public List<Photo> getAllPhotos() {

		List<Photo> result = null;

		Cursor cursor = db.query(PhotoTableMeta.DATABASE_TABLE, new String[] {
				PhotoTableMeta.KEY_ROWID,
				PhotoTableMeta.KEY_PATH,
				PhotoTableMeta.KEY_EXCURSIONID },
				null,
				null,
				null,
				null,
				null);

		if(cursor != null) {

			result = new ArrayList<Photo>();

			while(cursor.moveToNext()) {

				Photo photo = cursorToPhoto(cursor);

				result.add(photo);
			}

			cursor.close();
		}

		return result;
	}
	
	
	public Cursor getAllPhotosAsCursor() {

		Cursor cursor = db.query(PhotoTableMeta.DATABASE_TABLE, new String[] {
				PhotoTableMeta.KEY_ROWID,
				PhotoTableMeta.KEY_PATH,
				PhotoTableMeta.KEY_EXCURSIONID },
				null,
				null,
				null,
				null,
				null);

		return cursor;
	}
	

	public Photo getPhoto(long id) {

		Photo result = null;

		Cursor cursor = db.query(PhotoTableMeta.DATABASE_TABLE, new String[] {
				PhotoTableMeta.KEY_ROWID,
				PhotoTableMeta.KEY_PATH,
				PhotoTableMeta.KEY_EXCURSIONID },
				PhotoTableMeta.KEY_ROWID + "=" + id,
				null,
				null,
				null,
				null);

		if (cursor != null) {
			cursor.moveToFirst();

			result = cursorToPhoto(cursor);

			cursor.close();
		}

		return result;
	}
	
	
	public List<Photo> getAllPhotosByExcursion(long excursionId) {

		List<Photo> result = null;

		Cursor cursor = db.query(PhotoTableMeta.DATABASE_TABLE, new String[] {
				PhotoTableMeta.KEY_ROWID,
				PhotoTableMeta.KEY_PATH,
				PhotoTableMeta.KEY_EXCURSIONID },
				PhotoTableMeta.KEY_EXCURSIONID + "=" + excursionId ,
				null,
				null,
				null,
				null);

		if(cursor != null) {

			result = new ArrayList<Photo>();

			while(cursor.moveToNext()) {

				Photo photo = cursorToPhoto(cursor);

				result.add(photo);
			}

			cursor.close();
		}

		return result;
	}

	
	public boolean updatePhoto(Photo photo) {

		ContentValues args = new ContentValues();

		args.put(PhotoTableMeta.KEY_PATH, photo.getPath());
		args.put(PhotoTableMeta.KEY_LATITUDE, photo.getLatitude());
		args.put(PhotoTableMeta.KEY_LONGITUDE, photo.getLongitude());
		args.put(PhotoTableMeta.KEY_EXCURSIONID, photo.getExcursionId());

		return (db.update(PhotoTableMeta.DATABASE_TABLE, args, PhotoTableMeta.KEY_ROWID + "=" + photo.getId(), null) > 0);
	}

	
	private Photo cursorToPhoto(Cursor cursor) {

		Photo photo = null;

		if(cursor != null) {
			
			photo = new Photo();
			
			int idCol = cursor.getColumnIndexOrThrow(PhotoTableMeta.KEY_ROWID);
			int pathCol = cursor.getColumnIndexOrThrow(PhotoTableMeta.KEY_PATH);
			int excursionIdCol = cursor.getColumnIndexOrThrow(PhotoTableMeta.KEY_EXCURSIONID);

			photo.setId(cursor.getInt(idCol));
			photo.setPath(cursor.getString(pathCol));
			photo.setExcursionId(cursor.getInt(excursionIdCol));
		}

		return photo;
	}
	
	
	public boolean deletePhotoFile(String path) {
		File photo = new File(path);
		return photo.delete();
	}
	
	
	public boolean deleteAllPhotoFilesByExcursion(long excursionId) {
		boolean result = true;
		
		List<Photo> photos = getAllPhotosByExcursion(excursionId);
		
		for(Photo photo : photos) {
			result = (result && deletePhotoFile(photo.getPath()));
		}
		
		return result;
	}
}
